﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FDA_Code
{
    /// <summary>
    /// MsSqlHelper 的摘要说明
    /// </summary>
    public class MsSqlHelper
    {
        private SqlConnection conn;

        //private string connection = ConfigurationManager.ConnectionStrings["mssqlConnectionString"].ToString();
        private string connection = "Data Source=192.168.1.8;Initial Catalog=wms;Persist Security Info=True;User ID=sa;Password=nif_001;Pooling=True";
        //public string Erpconnection = ConfigurationManager.ConnectionStrings["ERPmssqlConnectionString"].ToString();

        //private string connection = Common.Kdnconnection;
        //public string Erpconnection = Common.Erpconnection;
        public MsSqlHelper()
        {
            Conn = new SqlConnection(connection);
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>  
        /// 执行不带参数的SQL语句  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public int ExecuteSql(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                int num = Convert.ToInt16(cmd.ExecuteNonQuery());
                conn.Close();
                return (num == 0) ? 0 : num;
                //return 1;
            }
        }

        public int ExecuteSql(String Sqlstr, String mssqlConnect)
        {
            using (SqlConnection conn = new SqlConnection(mssqlConnect))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                int num = Convert.ToInt16(cmd.ExecuteNonQuery());
                conn.Close();
                return (num == 0) ? 0 : num;
                //return 1;
            }
        }

        /// <summary>  
        /// 返回DataReader  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public SqlDataReader XExecuteReader(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                SqlCommand cmd = new SqlCommand(Sqlstr, conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                conn.Close();
                return dr;
            }
        }

        public SqlDataReader XExecuteReader(String Sqlstr, String mssqlConnect)
        {
            using (SqlConnection conn = new SqlConnection(mssqlConnect))
            {
                SqlCommand cmd = new SqlCommand(Sqlstr, conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                conn.Close();
                return dr;
            }
        }
        public SqlDataReader ExecuteReader(String Sqlstr)
        {
            SqlConnection conn = new SqlConnection(connection);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
                //sconn.Close();
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }
        public SqlDataReader ExecuteReader(String Sqlstr, String mssqlConnect)
        {
            SqlConnection conn = new SqlConnection(mssqlConnect);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
                //sconn.Close();
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }

        /// <summary>  
        /// 执行SQL语句并返回数据表  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataTable ExecuteDt(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        public DataTable ExecuteDt(String Sqlstr, String mssqlConnect)
        {
            using (SqlConnection conn = new SqlConnection(mssqlConnect))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>  
        /// 执行SQL语句并返回DataSet  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataSet ExecuteDs(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回DataSet 
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="mssqlConnect">数据库链接</param>
        /// <returns></returns>
        public DataSet ExecuteDs(String Sqlstr, String mssqlConnect)
        {
            using (SqlConnection conn = new SqlConnection(mssqlConnect))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public string GetString(string Sqlstr)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 与GetString(string Sqlstr, string field)区分
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="mssqlConnect"></param>
        /// <returns></returns>
        public string GetStringX(string Sqlstr, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr,mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="i">第i个字段</param>
        /// <returns></returns>
        public string GetString(string Sqlstr,int i)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][i].ToString();
            }
            return "";
        }
        public string GetString(string Sqlstr, int i, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr, mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][i].ToString();
            }
            return "";
        }
        /// <summary>
        /// 执行SQL语句并返回一个文本值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="field">字段名</param>
        /// <returns></returns>
        public string GetString(string Sqlstr, string field)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][field].ToString();
            }
            return "";
        }
        public string GetString(string Sqlstr, string field, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr,mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][field].ToString();
            }
            return "";
        }


        /// <summary>
        /// 快递鸟专用获取SQL
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="field"></param>
        /// <param name="mssqlConnect"></param>
        /// <returns></returns>
        public string GetSqlString(string Sqlstr, string field)
        {
            DataTable dt = ExecuteDt(Sqlstr, connection);
            if (dt != null && dt.Rows.Count > 0)
            {
                return btos((byte[])dt.Rows[0][field]);
            }
            return "";
        }
        public string btos(byte[] value)
        {

            Byte[] changde = Encoding.Convert(Encoding.GetEncoding("gb2312"), Encoding.UTF8, (byte[])value);
            System.IO.MemoryStream stream = new System.IO.MemoryStream(changde);
            System.IO.StreamReader sreader = new System.IO.StreamReader(stream);
            string text = sreader.ReadToEnd();
            return text;
        }
        /// <summary>
        /// 执行SQL语句并返回值是否存在 
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public bool Getbool(string Sqlstr)
        {
            DataTable dt = ExecuteDt(Sqlstr);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public bool Getbool(string Sqlstr, String mssqlConnect)
        {
            DataTable dt = ExecuteDt(Sqlstr,mssqlConnect);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        

        public SqlConnection Conn
        {
            get
            {
                return conn;
            }

            set
            {
                conn = value;
            }
        }
    }
}

